SET NOCOUNT ON
GO
USE [VNPT_Common]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_users') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_users
GO
CREATE PROCEDURE InsertUpdateDelete_users

	@id bigint output,
	@nguoidung nvarchar(50) = null ,
	@matkhau nvarchar(50) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into users values( @nguoidung, @matkhau)
		SELECT @id=SCOPE_IDENTITY();
	end
else if @Action=1
	begin
		update users set nguoidung=@nguoidung,matkhau=@matkhau where id=@id
	end
else if @Action=2
	begin
		delete users where id=@id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_positions') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_positions
GO
CREATE PROCEDURE InsertUpdateDelete_positions

	@id int output,
	@name nvarchar(50) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into positions values( @name)
		SELECT @id=SCOPE_IDENTITY();
	end
else if @Action=1
	begin
		update positions set name=@name where id=@id
	end
else if @Action=2
	begin
		delete positions where id=@id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_department_permission') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_department_permission
GO
CREATE PROCEDURE InsertUpdateDelete_department_permission

	@department_id int ,
	@screen_id int ,
	@permission nvarchar(1000) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into department_permission values( @department_id, @screen_id, @permission)
	end
else if @Action=1
	begin
		update department_permission set screen_id=@screen_id,permission=@permission where department_id=@department_id
	end
else if @Action=2
	begin
		delete department_permission where department_id=@department_id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_religious') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_religious
GO
CREATE PROCEDURE InsertUpdateDelete_religious

	@id int output,
	@name nvarchar(100) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into religious values( @name)
		SELECT @id=SCOPE_IDENTITY();
	end
else if @Action=1
	begin
		update religious set name=@name where id=@id
	end
else if @Action=2
	begin
		delete religious where id=@id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_ethnics') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_ethnics
GO
CREATE PROCEDURE InsertUpdateDelete_ethnics

	@id int output,
	@name nvarchar(100) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into ethnics values( @name)
		SELECT @id=SCOPE_IDENTITY();
	end
else if @Action=1
	begin
		update ethnics set name=@name where id=@id
	end
else if @Action=2
	begin
		delete ethnics where id=@id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_user_permision') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_user_permision
GO
CREATE PROCEDURE InsertUpdateDelete_user_permision

	@screen_id int ,
	@user_id int ,
	@permission varbinary(1000) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into user_permision values( @screen_id, @user_id, @permission)
	end
else if @Action=1
	begin
		update user_permision set user_id=@user_id,permission=@permission where screen_id=@screen_id
	end
else if @Action=2
	begin
		delete user_permision where screen_id=@screen_id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_states') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_states
GO
CREATE PROCEDURE InsertUpdateDelete_states

	@code varchar(8) ,
	@name nvarchar(100) = null ,
	@parent_id int = null ,
	@slug nvarchar(500) = null ,
	@type int = null ,
	@geo varchar(100) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into states values( @code, @name, @parent_id, @slug, @type, @geo)
	end
else if @Action=1
	begin
		update states set name=@name,parent_id=@parent_id,slug=@slug,type=@type,geo=@geo where code=@code
	end
else if @Action=2
	begin
		delete states where code=@code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_branch') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_branch
GO
CREATE PROCEDURE InsertUpdateDelete_branch

	@code varchar(10) ,
	@name nchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into branch values( @code, @name)
	end
else if @Action=1
	begin
		update branch set name=@name where code=@code
	end
else if @Action=2
	begin
		delete branch where code=@code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_user_info') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_user_info
GO
CREATE PROCEDURE InsertUpdateDelete_user_info

	@user_id int ,
	@firstname nvarchar(50) = null ,
	@lastname nvarchar(50) = null ,
	@department_code varchar(10) = null ,
	@branch_code varchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into user_info values( @user_id, @firstname, @lastname, @department_code, @branch_code)
	end
else if @Action=1
	begin
		update user_info set firstname=@firstname,lastname=@lastname,department_code=@department_code,branch_code=@branch_code where user_id=@user_id
	end
else if @Action=2
	begin
		delete user_info where user_id=@user_id
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_department') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_department
GO
CREATE PROCEDURE InsertUpdateDelete_department

	@code varchar(10) ,
	@name nvarchar(500) = null ,
	@parent_id int = null ,
	@Action int
AS

if @Action=0
	begin
		insert into department values( @code, @name, @parent_id)
	end
else if @Action=1
	begin
		update department set name=@name,parent_id=@parent_id where code=@code
	end
else if @Action=2
	begin
		delete department where code=@code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_screen') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_screen
GO
CREATE PROCEDURE InsertUpdateDelete_screen

	@id int ,
	@name nvarchar(200) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into screen values( @id, @name)
	end
else if @Action=1
	begin
		update screen set name=@name where id=@id
	end
else if @Action=2
	begin
		delete screen where id=@id
	end
RETURN
GO
----------------------------------------------------------------
if object_id('spGetUser','P') is not null
	drop proc spGetUser
go
create proc spGetUser @user nvarchar(100)=null, @pass nvarchar(50)=null,
	@branch_code varchar(10)=null,@department_code varchar(10)=null
as
declare @query varchar(MAX)
set @query='SELECT * FROM users u join user_info i on u.id=i.user_id'
declare @where varchar(MAX)
set @where=''
if(@user is not null)
	if(@user<>'') set @where=@where +' AND '
	set @where=@where+' u.nguoidung='''+@user+''''
if(@pass is not null)
	if(@pass<>'') set @where=@where +' AND '
	set @where=@where+' u.matkhau='''+@pass+''''
if(@branch_code is not null)
	if(@branch_code<>'') set @where=@where +' AND '
	set @where=@where+' i.branch_code='''+@branch_code+''''
if(@department_code is not null)
	if(@department_code<>'') set @where=@where +' AND '
	set @where=@where+' i.department_code='''+@department_code+''''
if(@where <>'')set @query=@query+' WHERE '+@where
exec(@query)
